﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for InvoiceDAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class HOA_DON_DAO
    {

        private const string SQL_SELECT_ALL_UNPAID_INVOICE = "select * from vwInvoice where Overday>@SO_NGAY_QUA_HAN";
        private const string SQL_SELECT_UNPAID_INVOICE_NONE_CLASS = "select * from vwInvoiceWithoutClass where Overday>@SO_NGAY_QUA_HAN";
        private const string SQL_SELECT_BY_CLASS = "select * from vwInvoice where MA_LOP_HOC=@MA_LOP_HOC and Overday>@SO_NGAY_QUA_HAN";
        private const string SQL_SELECT_ALL = "select MA_HOA_DON,SINH_VIEN.MA_SV, TEN_SV,LOAILOP.MA_LOAI_LOP,TEN_LOAI_LOP,MUC_PHI.MA_MUC_PHI from HOA_DON join SINH_VIEN on HOA_DON.MA_SV = SINH_VIEN.MA_SV join LOAI_LOP on LOAI_LOP.MA_LOAI_LOP = HOA_DON.MA_LOAI_LOP join MUC_PHI on MUC_PHI.MA_MUC_PHI = HOA_DON.MA_MUC_PHI";
        private const string SQL_INSERT = "insert into HOA_DON(MA_SV, MA_LOAI_LOP, MA_MUC_PHI,DANG_KY_NHOM,NGAY_KICH_HOAT, GHI_CHU) values(@MA_SV, @MA_LOAI_LOP, @MA_MUC_PHI, @DANG_KY_NHOM, @NGAY_KICH_HOAT, @GHI_CHU)";
        private const string SQL_UPDATE = "update HOA_DON set MA_LOAI_LOP = @MA_LOAI_LOP, MA_MUC_PHI = @MA_MUC_PHI,DANG_KY_NHOM = @DANG_KY_NHOM , GHI_CHU = @GHI_CHU where MA_HOA_DON = @MA_HOA_DON";
        private const string SQL_REUPDATE = "update HOA_DON set MA_LOP_HOC = @MA_LOP_HOC where MA_HOA_DON = @MA_HOA_DON";
        private const string SQL_ACTIVATE = "update HOA_DON set NGAY_KICH_HOAT=getdate(), KICH_HOAT = @KICH_HOAT where MA_HOA_DON = @MA_HOA_DON";
        private const string SQL_DELETE = "delete from HOA_DON where MA_HOA_DON = @MA_HOA_DON";
        private const string SQL_SELECT_UNACTIVATED_INVOICE_0 = "select * from vwActivation  WHERE  (KICH_HOAT = 0) and (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV)";
        private const string SQL_SELECT_UNACTIVATED_INVOICE = "select * from vwActivation  WHERE  (KICH_HOAT = 0) and (MA_DOI_TUONG like @MA_LOAI_LOP) and (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV)";
        private const string SQL_SELECT_ACTIVATED_INVOICE = "select * from vwActivation where (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV) and KICH_HOAT =1";
        private const string SQL_SELECT_ACTIVATED_INVOICE2 = "select * from vwActivation where (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV) and MA_DOI_TUONG = @MA_LOAI_LOP and KICH_HOAT=1";
        private const string SQL_SELECT_UNSPEC_CLASS_INVOICE = "SELECT     HOA_DON.MA_HOA_DON, SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, SINH_VIEN.EMAIL, "
                + " SINH_VIEN.GIOI_TINH, DOI_TUONG.MA_DOI_TUONG, DOI_TUONG.TEN_DOI_TUONG, HOA_DON.MA_LOP_HOC, HOA_DON.NGAY_KICH_HOAT, HOA_DON.GHI_CHU"
                + " FROM         Student INNER JOIN"
                + " HOA_DON ON SINH_VIEN.SINH_VIEN = HOA_DON.MA_SV INNER JOIN"
                + " DOI_TUONG ON HOA_DON.MA_LOAI_LOP = DOI_TUONG.MA_DOI_TUONG"
                + " WHERE     (HOA_DON.MA_LOP_HOC < 0) and MA_LOAI_LOP like @MA_LOAI_LOP";
        private const string SQL_SELECT_SPEC_CLASS_INVOICE = "SELECT     HOA_DON.MA_HOA_DON, SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, SINH_VIEN.EMAIL, "
               + " SINH_VIEN.GIOI_TINH, DOI_TUONG.MA_DOI_TUONG, DOI_TUONG.TEN_DOI_TUONG, HOA_DON.NGAY_KICH_HOAT, Invoice.GHI_CHU, Class.MA_LOP_HOC, Class.TEN_LOP_HOC"
               + " FROM         SINH_VIEN INNER JOIN"
               + " HOA_DON ON SINH_VIEN.MA_SV = HOA_DON.SINH_VIEN INNER JOIN"
               + " DOI_TUONG ON HOA_DON.MA_LOAI_LOP = DOI_TUONG.MA_DOI_TUONG INNER JOIN"
               + " LOP_HOC ON HOA_DON.MA_LOP_HOC = LOP_HOC.MA_LOP_HOC "
               + " WHERE     (HOA_DON.MA_LOP_HOC > 0) AND (HOA_DON.MA_LOAI_LOP LIKE @MA_LOAI_LOP) and TEN_LOP_HOC like @TEN_LOP_HOC  order by Class";
        private const string SQL_SELECT_ACTIVATED_INVOICE_BY_NAME_AND_NONECLASS = "SELECT     HOA_DON.MA_HOA_DON, SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, SINH_VIEN.EMAIL, "
            + " SINH_VIEN.GIOI_TINH, DOI_TUONG.MA_DOI_TUONG, DOI_TUONG.TEN_DOI_TUONG,MUC_PHI.MA_MUC_PHI,MUC_PHI.SO_LAN_THANH_TOAN ,MUC_PHI.TONG_TIEN, HOA_DON.MA_LOP_HOC, HOA_DON.NGAY_KICH_HOAT,HOA_DON.DANG_KY_NHOM, HOA_DON.GHI_CHU, dbo.GetTotalFeeByInvoice(HOA_DON.MA_HOA_DON, HOA_DON.MA_LOAI_LOP) as PaidAmount"
            + " FROM         SINH_VIEN INNER JOIN"
            + " HOA_DON ON SINH_VIEN.MA_SV = HOA_DON.MA_SV INNER JOIN"
            + " DOI_TUONG ON HOA_DON.MA_LOAI_LOP = DOI_TUONG.MA_DOI_TUONG inner join MUC_PHI on HOA_DON.MA_MUC_PHI= MUC_PHI.MA_MUC_PHI"
            + " WHERE (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV) and  (HOA_DON.MA_LOP_HOC <= 0)";
        private const string SQL_SELECT_ACTIVATED_INVOICE_BY_NAME_AND_CLASS_0 = "SELECT     HOA_DON.MA_HOA_DON, SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, SINH_VIEN.EMAIL, "
            + " SINH_VIEN.GIOI_TINH, DOI_TUONG.MA_DOI_TUONG, DOI_TUONG.TEN_DOI_TUONG, HOA_DON.NGAY_KICH_HOAT,HOA_DON.DANG_KY_NHOM, HOA_DON.GHI_CHU, LOP_HOC.MA_LOP_HOC, LOP_HOC.TEN_LOP_HOC, "
            + " dbo.GetTotalFeeByInvoice(HOA_DON.MA_HOA_DON, HOA_DON.MA_LOAI_LOP) AS PaidAmount,MUC_PHI.MA_MUC_PHI ,MUC_PHI.SO_LAN_THANH_TOAN, MUC_PHI.TONG_TIEN, MUC_PHI.THOI_HAN"
            + " FROM         SINH_VIEN INNER JOIN"
            + " HOA_DON ON SINH_VIEN.MA_SV = HOA_DON.MA_SV INNER JOIN"
            + " DOI_TUONG ON HOA_DON.MA_LOAI_LOP = DOI_TUONG.MA_DOI_TUONG INNER JOIN"

            + " MUC_PHI ON HOA_DON.MA_MUC_PHI = MUC_PHI.MA_MUC_PHI"
             + " join LOP_HOC on HOA_DON.MA_LOP_HOC= LOP_HOC.MA_LOP_HOC"
            + " WHERE   (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV)";
        private const string SQL_SELECT_ACTIVATED_INVOICE_BY_NAME_AND_CLASS = "SELECT     HOA_DON.MA_HOA_DON, SINH_VIEN.MA_SV, SINH_VIEN.MA_SO_SV, SINH_VIEN.TEN_SV, SINH_VIEN.NGAY_SINH, SINH_VIEN.DIA_CHI, SINH_VIEN.SO_DIEN_THOAI, SINH_VIEN.EMAIL, "
            + " SINH_VIEN.GIOI_TINH, DOI_TUONG.MA_DOI_TUONG, DOI_TUONG.TEN_DOI_TUONG, HOA_DON.NGAY_KICH_HOAT,HOA_DON.DANG_KY_NHOM, HOA_DON.GHI_CHU, LOP_HOC.MA_LOP_HOC, LOP_HOC.TEN_LOP_HOC, "
            + " dbo.GetTotalFeeByInvoice(HOA_DON.MA_HOA_DON, HOA_DON.MA_LOAI_LOP) AS PaidAmount,MUC_PHI.MA_MUC_PHI ,MUC_PHI.SO_LAN_THANH_TOAN, MUC_PHI.TONG_TIEN, MUC_PHI.THOI_HAN"
            + " FROM         SINH_VIEN INNER JOIN"
            + " HOA_DON ON SINH_VIEN.MA_SV = HOA_DON.MA_SV INNER JOIN"
            + " DOI_TUONG ON HOA_DON.MA_LOAI_LOP = DOI_TUONG.MA_DOI_TUONG INNER JOIN"
            + " LOP_HOC ON HOA_DON.MA_LOP_HOC = LOP_HOC.MA_LOP_HOC INNER JOIN"
            + " MUC_PHI ON HOA_DON.MA_MUC_PHI = MUC_PHI.MA_MUC_PHI"

            + " WHERE   (TEN_SV like @TEN_SV or MA_SO_SV like @MA_SO_SV) and (HOA_DON.MA_LOP_HOC like @MA_LOP_HOC)";
        /****** View bien lai ***/
        private const string SQL_SELECT_CONSUM_BY_ALL = "select MA_HOA_DON, MA_SV, MA_SO_SV, TEN_SV,MA_DOI_TUONG, TEN_DOI_TUONG,LOP, sum(PaidAmount) as PaidAmount from vwReceipt" + " where NGAY_THU_PHI>=@FROM_DATE and NGAY_THU_PHI<= @TO_DATE" + " group by MA_HOA_DON, MA_SV, MA_SO_SV, TEN_SV,MA_DOI_TUONG, TEN_DOI_TUONG,LOP  having LOP like @LOP";
        private const string SQL_SELECT_CONSUM_BY_OBJCLASS = "select MA_HOA_DON, MA_SV, MA_SO_SV, TEN_SV,MA_DOI_TUONG, TEN_DOI_TUONG,LOP, sum(PaidAmount) as PaidAmount from vwReceipt" + " where NGAY_THU_PHI>=@FROM_DATE and NGAY_THU_PHI<= @TO_DATE" + " group by MA_HOA_DON, MA_SV, MA_SO_SV, TEN_SV,MA_DOI_TUONG, TEN_DOI_TUONG,LOP  having LOP =@LOP";
        private const string SQL_SELECT_SUM = "select sum(PaidAmount) from vwReceipt";
        private const string SQL_SELECT_INVOICE_COUNT = "select count(MA_HOA_DON) from (select distinct MA_HOA_DON from vwReceipt where NGAY_THU_PHI>=@NGAY_THU_PHI and NGAY_THU_PHI<=@NGAY_THU_PHI) as A";
        private const string SQL_SELECT_TIME_COUNT = "select count(*) from vwReceipt where NGAY_THU_PHI>=@NGAY_THU_PHI and NGAY_THU_PHI<=@NGAY_THU_PHI";
        // Parameters
        private const string PARM_INVOICE_ID = "@MA_HOA_DON";
        private const string PARM_STUDENT_ID = "@MA_SV";
        private const string PARM_CLASS_ID = "@MA_LOP_HOC";
        private const string PARM_CLASS_TYPE_ID = "@MA_LOAI_LOP";
        private const string PARM_FEE_LEVEL_ID = "@MA_MUC_PHI";
        private const string PARM_REGISTER_IN_GROUP = "@DANG_KY_NHOM";
        private const string PARM_ACTIVATED_DATE = "@NGAY_KICH_HOAT";
        private const string PARM_GHI_CHU = "@GHI_CHU";
        private const string PARM_IS_ACTIVATED = "@KICH_HOAT";
        private const string PARM_STUDENT_CODE = "@MA_SO_SV";
        private const string PARM_STUDENT_NAME = "@TEN_SV";
        private const string PARM_CLASS_NAME = "@TEN_LOP_HOC";
        private const string PARM_OVERDAYS = "@SO_NGAY_QUA_HAN";
        //
        private const string PARM_FROM_DATE = "@FROM_DATE";
        private const string PARM_TO_DATE = "@TO_DATE";        
        private const string PARM_OBJECT_CLASS = "@LOP";
        #region Public Methods
        public IList<HOA_DON> GetAll()
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));

                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));

                    if (!reader.IsDBNull(14)) invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_LOAI_LOP"));
                    if (!reader.IsDBNull(15)) invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_LOAI_LOP"));

                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public Boolean Insert(HOA_DON invoice, out int newId)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_STUDENT_ID, SqlDbType.Int),
											 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_FEE_LEVEL_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_REGISTER_IN_GROUP, SqlDbType.Int),
                                             new SqlParameter(PARM_ACTIVATED_DATE, SqlDbType.DateTime),
                                             new SqlParameter(PARM_GHI_CHU, SqlDbType.NVarChar)};
                parms[0].Value = invoice.SINH_VIEN.MA_SV;
                parms[1].Value = invoice.MA_LOAI_LOP.MA_DOI_TUONG;
                parms[2].Value = invoice.MA_MUC_PHI.MA_MUC_PHI;
                parms[3].Value = invoice.DANG_KY_NHOM;
                parms[4].Value = invoice.NGAY_KICH_HOAT;
                parms[5].Value = invoice.GHI_CHU;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT, parms);
                    newId = int.Parse(SQLDBHelper.ExecuteScalar(conn, CommandType.Text, "select @@IDENTITY from HOA_DON", null).ToString());
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    newId = 0;
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(HOA_DON invoice)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_FEE_LEVEL_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_REGISTER_IN_GROUP, SqlDbType.Int),
                                             new SqlParameter(PARM_GHI_CHU, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int)};
            parms[0].Value = invoice.MA_LOAI_LOP.MA_DOI_TUONG;
            parms[1].Value = invoice.MA_MUC_PHI.MA_MUC_PHI;
            parms[2].Value = invoice.DANG_KY_NHOM;
            parms[3].Value = invoice.GHI_CHU;
            parms[4].Value = invoice.MA_HOA_DON;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        public bool Reupdate(HOA_DON hdon)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int),
                                             new SqlParameter(PARM_CLASS_ID, SqlDbType.Int)};

            parms[0].Value = hdon.MA_HOA_DON;
            parms[1].Value = hdon.MA_LOP_HOC.MA_LOP_HOC;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_REUPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
        }


        public bool Delete(int MA_HOA_DON)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int) };
            parms[0].Value = MA_HOA_DON;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool KICH_HOAT(HOA_DON invoice)
        {
            SqlParameter[] parms;

            parms = new SqlParameter[] {
                                                 new SqlParameter(PARM_IS_ACTIVATED, SqlDbType.Bit),                             
                                                 new SqlParameter(PARM_INVOICE_ID, SqlDbType.Int)};
            parms[0].Value = invoice.KICH_HOAT;
            parms[1].Value = invoice.MA_HOA_DON;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_ACTIVATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public IList<HOA_DON> GetUnactivatedInvoices(string key, int MA_LOAI_LOP)
        {
            string SQL = SQL_SELECT_UNACTIVATED_INVOICE_0;
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            if (MA_LOAI_LOP <= 0)
            {
                parms = new SqlParameter[] {                                                                           
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar)};
                parms[0].Value = key;
                parms[1].Value = key;
            }
            else
            {
                SQL = SQL_SELECT_UNACTIVATED_INVOICE;
                parms = new SqlParameter[] {
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.NVarChar),                             
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar)};
                parms[0].Value = MA_LOAI_LOP;
                parms[1].Value = "%" + key + "%";
                parms[2].Value = "%" + key + "%";
            }


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.DANG_KY_NHOM = reader.GetInt32(reader.GetOrdinal("DANG_KY_NHOM"));
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));

                    invoice.MA_LOP_HOC = new LOP_HOC();
                    invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));

                    invoice.MA_LOAI_LOP = new DOI_TUONG();
                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoice.MA_MUC_PHI = new MUC_PHI();
                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public IList<HOA_DON> GetActivatedInvoices(string key, int MA_LOAI_LOP)
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            string SQL;
            if (MA_LOAI_LOP <= 0)
            {
                parms = new SqlParameter[] {                             
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar)};
                parms[0].Value = "%" + key + "%";
                parms[1].Value = "%" + key + "%";
                SQL = SQL_SELECT_ACTIVATED_INVOICE;
            }
            else
            {
                parms = new SqlParameter[] {                            
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int)};
                parms[0].Value = "%" + key + "%";
                parms[1].Value = "%" + key + "%";
                parms[2].Value = MA_LOAI_LOP;
                SQL = SQL_SELECT_ACTIVATED_INVOICE2;
            }

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.DANG_KY_NHOM = reader.GetInt32(reader.GetOrdinal("DANG_KY_NHOM"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));

                    invoice.MA_LOP_HOC = new LOP_HOC();
                    invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));

                    invoice.MA_LOAI_LOP = new DOI_TUONG();
                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoice.MA_MUC_PHI = new MUC_PHI();
                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));

                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public bool CreateViewActivatedInvoices()
        {
            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, "EXEC createViewActivation", null);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public IList<HOA_DON> GetInvoicesWithoutClass(int MA_LOAI_LOP)
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            if (MA_LOAI_LOP < 0)
            {
                parms = new SqlParameter[] {                             
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.NVarChar)};
                parms[0].Value = "%";
            }
            else
            {
                parms = new SqlParameter[] {                            
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int)};
                parms[0].Value = MA_LOAI_LOP;
            }

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_UNSPEC_CLASS_INVOICE, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("SINH_VIEN"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));

                    invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));

                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public IList<HOA_DON> GetInvoicesHadClass(int MA_LOAI_LOP, string TEN_LOP_HOC)
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            if (MA_LOAI_LOP < 0)
            {
                parms = new SqlParameter[] {                             
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_CLASS_NAME, SqlDbType.NVarChar)};

                parms[0].Value = "%";
            }
            else
            {
                parms = new SqlParameter[] {                            
                                                 new SqlParameter(PARM_CLASS_TYPE_ID, SqlDbType.Int),
                                                 new SqlParameter(PARM_CLASS_NAME, SqlDbType.NVarChar)};
                parms[0].Value = MA_LOAI_LOP;
            }
            parms[1].Value = "%" + TEN_LOP_HOC + "%";

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_SPEC_CLASS_INVOICE, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("SINH_VIEN"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));

                    invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    invoice.MA_LOP_HOC.TEN_LOP_HOC = reader.GetString(reader.GetOrdinal("TEN_LOP_HOC"));

                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public IList<HOA_DON> GetActivatedInvoicesWithoutClass(string key)
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {                             
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar)};
            parms[0].Value = "%" + key + "%";
            parms[1].Value = "%" + key + "%";

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ACTIVATED_INVOICE_BY_NAME_AND_NONECLASS, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.DANG_KY_NHOM = reader.GetInt32(reader.GetOrdinal("DANG_KY_NHOM"));
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    if (!reader.IsDBNull(18)) invoice.THOI_HAN = reader.GetFloat(reader.GetOrdinal("PaidAmount"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));


                    invoice.MA_LOP_HOC = new LOP_HOC();
                    // if (!reader.IsDBNull(14)) invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    invoice.MA_LOAI_LOP = new DOI_TUONG();
                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));


                    invoice.MA_MUC_PHI = new MUC_PHI();
                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));
                    invoice.MA_MUC_PHI.TONG_TIEN = (float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        public IList<HOA_DON> GetActivatedInvoicesHadClass(string key, int MA_LOP_HOC)
        {
            IList<HOA_DON> invoices = new List<HOA_DON>();
            string query = SQL_SELECT_ACTIVATED_INVOICE_BY_NAME_AND_CLASS_0;
            SqlParameter[] parms;
            if (MA_LOP_HOC <= 0)
            {
                parms = new SqlParameter[] {                  
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar)
                                               };
                parms[0].Value = "%" + key + "%";
                parms[1].Value = "%" + key + "%";

            }
            else
            {
                parms = new SqlParameter[] {                             
                                                 new SqlParameter(PARM_STUDENT_CODE, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_STUDENT_NAME, SqlDbType.NVarChar),
                                                 new SqlParameter(PARM_CLASS_ID, SqlDbType.NVarChar)};
                parms[0].Value = "%" + key + "%";
                parms[1].Value = "%" + key + "%";
                parms[2].Value = MA_LOP_HOC;
            }

            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, query, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));
                    invoice.DANG_KY_NHOM = reader.GetInt32(reader.GetOrdinal("DANG_KY_NHOM"));
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    if (!reader.IsDBNull(16)) invoice.THOI_HAN = (float)reader.GetDouble(reader.GetOrdinal("PaidAmount"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));


                    invoice.MA_LOP_HOC = new LOP_HOC();

                    if (!reader.IsDBNull(14)) invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    if (!reader.IsDBNull(15)) invoice.MA_LOP_HOC.TEN_LOP_HOC = reader.GetString(reader.GetOrdinal("TEN_LOP_HOC"));

                    invoice.MA_LOAI_LOP = new DOI_TUONG();
                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoice.MA_MUC_PHI = new MUC_PHI();
                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));
                    invoice.MA_MUC_PHI.TONG_TIEN = (float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));
                    invoice.MA_MUC_PHI.THOI_HAN = reader.GetInt32(reader.GetOrdinal("THOI_HAN"));

                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        /********* vwInvoice ******/
        public IList<HOA_DON> QueryInvoiceView(int soNgayQuaHan, int maLopHoc)
        {
            string SQL = SQL_SELECT_ALL_UNPAID_INVOICE;
            IList<HOA_DON> invoices = new List<HOA_DON>();
            SqlParameter[] parms;
            if (maLopHoc <= 0)
            {
                parms = new SqlParameter[] {                                                                           
                                                
                                                 new SqlParameter(PARM_OVERDAYS, SqlDbType.Int)};
                parms[0].Value = soNgayQuaHan;
              
            }
            else
            {
                SQL = SQL_SELECT_BY_CLASS;
                parms = new SqlParameter[] {
                                                 new SqlParameter(PARM_CLASS_ID, SqlDbType.Int),                            
                                                
                                                 new SqlParameter(PARM_OVERDAYS, SqlDbType.Int)};
                parms[0].Value = maLopHoc;
                parms[1].Value = soNgayQuaHan;
             
            }


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL, parms))
            {
                while (reader.Read())
                {
                    HOA_DON invoice = new HOA_DON();

                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));
                    invoice.NGAY_KICH_HOAT = reader.GetDateTime(reader.GetOrdinal("NGAY_KICH_HOAT"));                  
                    invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));
                    invoice.SINH_VIEN.NGAY_SINH = reader.GetDateTime(reader.GetOrdinal("NGAY_SINH"));
                    invoice.SINH_VIEN.GIOI_TINH = reader.GetBoolean(reader.GetOrdinal("GIOI_TINH"));
                    invoice.SINH_VIEN.DIA_CHI = reader.GetString(reader.GetOrdinal("DIA_CHI"));
                    invoice.SINH_VIEN.EMAIL = reader.GetString(reader.GetOrdinal("EMAIL"));
                    invoice.SINH_VIEN.SO_DIEN_THOAI = reader.GetString(reader.GetOrdinal("SO_DIEN_THOAI"));
                    

                    invoice.MA_LOP_HOC = new LOP_HOC();
                    invoice.MA_LOP_HOC.MA_LOP_HOC = reader.GetInt32(reader.GetOrdinal("MA_LOP_HOC"));
                    invoice.MA_LOP_HOC.TEN_LOP_HOC=reader.GetString(reader.GetOrdinal("TEN_LOP_HOC"));
                    invoice.MA_LOP_HOC.CA_HOC = reader.GetString(reader.GetOrdinal("CA_HOC"));

                   // invoice.MA_LOAI_LOP = new DOI_TUONG();
                   // invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                   // invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));

                    invoice.MA_MUC_PHI = new MUC_PHI();
                    invoice.MA_MUC_PHI.MA_MUC_PHI = reader.GetInt32(reader.GetOrdinal("MA_MUC_PHI"));
                    invoice.MA_MUC_PHI.TONG_TIEN =(float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));
                    invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));

                    invoice.HOA_DON_CT = new HOA_DON_CT();
                    invoice.HOA_DON_CT.NGAY_THU_PHI = reader.GetDateTime(reader.GetOrdinal("NGAY_THU_PHI"));
                    invoice.HOA_DON_CT.SO_TIEN = (float)reader.GetDouble(reader.GetOrdinal("SO_TIEN"));
                    //invoice.HOA_DON_CT.GHI_CHU = reader.GetString(reader.GetOrdinal("SO_TIEN"));
                    invoice.THOI_HAN = reader.GetInt32(reader.GetOrdinal("OverDay"));
                    invoices.Add(invoice);
                }
            }
            return invoices;
        }

        /***** vwReceipt  *********/
        public IList<BIEN_LAI> QueryReceiptView(string lopDoiTuong, DateTime fromDate, DateTime toDate)
        {
            string SQL = null;
            IList<BIEN_LAI> dsBienLai = new List<BIEN_LAI>();
            SqlParameter[] parms;
            if (String.IsNullOrEmpty(lopDoiTuong))
            {
                parms = new SqlParameter[] {
                                                 new SqlParameter(PARM_FROM_DATE, SqlDbType.DateTime),                                                                           
                                                 new SqlParameter(PARM_TO_DATE, SqlDbType.DateTime),
                                                 new SqlParameter(PARM_OBJECT_CLASS, SqlDbType.NVarChar)
                };
                SQL = SQL_SELECT_CONSUM_BY_ALL;
                parms[0].Value = fromDate;
                parms[1].Value = toDate;
                parms[2].Value = "%" + lopDoiTuong +"%";
            }
            else
            {
                SQL = SQL_SELECT_CONSUM_BY_OBJCLASS;
                parms = new SqlParameter[] {
                                                 new SqlParameter(PARM_FROM_DATE, SqlDbType.DateTime),                                                                           
                                                 new SqlParameter(PARM_TO_DATE, SqlDbType.DateTime),
                                                 new SqlParameter(PARM_OBJECT_CLASS, SqlDbType.NVarChar)
                };
                parms[0].Value = fromDate;
                parms[1].Value = toDate;
                parms[2].Value = lopDoiTuong;
            }


            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL, parms))
            {
                while (reader.Read())
                {
                    BIEN_LAI bienlai = new BIEN_LAI();
                    HOA_DON invoice = new HOA_DON();
                    
                    invoice.MA_HOA_DON = reader.GetInt32(reader.GetOrdinal("MA_HOA_DON"));                   
                   // invoice.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));

                    invoice.SINH_VIEN = new SINH_VIEN();
                    invoice.SINH_VIEN.MA_SV = reader.GetInt32(reader.GetOrdinal("MA_SV"));
                    invoice.SINH_VIEN.MA_SO_SV = reader.GetString(reader.GetOrdinal("MA_SO_SV"));
                    invoice.SINH_VIEN.TEN_SV = reader.GetString(reader.GetOrdinal("TEN_SV"));
                   

                    invoice.MA_LOAI_LOP = new DOI_TUONG();
                    invoice.MA_LOAI_LOP.MA_DOI_TUONG = reader.GetInt32(reader.GetOrdinal("MA_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.TEN_DOI_TUONG = reader.GetString(reader.GetOrdinal("TEN_DOI_TUONG"));
                    invoice.MA_LOAI_LOP.LOP = reader.GetString(reader.GetOrdinal("LOP"));

                   //invoice.MA_MUC_PHI = new MUC_PHI();                    
                    //invoice.MA_MUC_PHI.TONG_TIEN = (float)reader.GetDouble(reader.GetOrdinal("TONG_TIEN"));
                    //invoice.MA_MUC_PHI.SO_LAN_THANH_TOAN = reader.GetInt32(reader.GetOrdinal("SO_LAN_THANH_TOAN"));

                    bienlai.MA_HOA_DON = invoice;
                    //bienlai.GHI_CHU = reader.GetString(reader.GetOrdinal("GHI_CHU"));
                    bienlai.SO_TIEN = (float)reader.GetDouble(reader.GetOrdinal("PaidAmount"));
                    
                    dsBienLai.Add(bienlai);
                }
            }
            return dsBienLai;
        }
        #endregion
    }
}